﻿using System;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Guest_room.DAL
{
    public class dbConnection
    {
        public SqlConnection cn;
        //private const string connectionString = "server=.;database=Guest_room;integrated security=true";
        private const string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Guest_room.mdf;Integrated Security=True";

        public SqlConnection GetCn()
        {
            if (cn == null || cn.State == ConnectionState.Closed)
            {
                cn = new SqlConnection(connectionString);
                cn.Open();
            }
            return cn;
        }

        public void CloseCn() { cn.Close(); }

        public DataTable GetDataSet(string sql) // 查询数据库
        {
            sql = sql.Trim();
            using (SqlCommand cmd = new SqlCommand(sql, cn)) // 执行sql语句
            {
                using (SqlDataAdapter da = new SqlDataAdapter(cmd)) // 将执行结果保存在适配器中
                {
                    DataSet ds = new DataSet(); // 创建数据集
                    da.Fill(ds); // 将适配器中的结果填充到数据集
                    return ds.Tables[0];
                }
            }
        }

        public bool SqlEx(string sql) // 对数据库的添加修改，删除操作
        {
            using (SqlCommand cmd = new SqlCommand(sql, cn))
            {
                try
                {
                    cmd.ExecuteNonQuery(); // 同步更新数据源
                    return true;
                }
                catch (SqlException)
                {
                    // MessageBox.Show("执行失败\n" + ex.Message.ToString());
                    return false;
                }
                finally
                {
                    cn.Close();
                }
            }
        }
    }
}
